package chapter03.chapler10.Demo1;

import java.sql.*;
import java.util.Scanner;

public class Demo1 {
        /**
         *模拟添加用户业务
         */
        public static void main(String[] args) throws SQLException {
            Statement stmt = null;
            ResultSet rs = null;
            Connection coon = null;
            try{
                // 1.注册数据库驱动
                Class.forName("com.mysql.cj.jdbc.Driver");
                // 2.通过DriverManger获取数据库连接
                String url = "jdbc:mysql://localhost:3306/jdbc?sereverTimezone=GMT%2B8&useSSL=false";
                String username = "root";//数据库账户
                String password = "123456";
                coon  = DriverManager.getConnection(url,username,password);
                // 3. 通过Connection对象获取Statement对象
                stmt = coon.createStatement();
                // 4. 使用Statement执行SQL语句
                /**
                 * 终端用户输入用户名 密码 邮箱
                 * 将终端用户的输入添加到数据库
                 */
                Scanner sc = new Scanner(System.in);
                System.out.println("请输入用户姓名:");
                String un = sc.next();
                System.out.println("请输入用户的密码:");
                String pd = sc.next();
                System.out.println("请输入用户的邮箱:");
                String em = sc.next();
                String insertSQL = "insert into users(name,password,email) values(?,?,?)";
                PreparedStatement pm = coon.prepareStatement(insertSQL);//对插入SQL进行处理
                pm.setString(1,un);//将SQL第一个？号绑定成un值
                pm.setString(2,un);//将SQL第二个？号绑定成pd值
                pm.setString(3,un);//将SQL第三个？号绑定成em值
                pm.executeUpdate();

                String sql = "select * from users";
                rs = stmt.executeQuery(sql);
                //5. 显示结果集合
                System.out.println("id   |    name    |    password   |   email    |   birthday");
                while (rs.next()){
                    int id = rs.getInt("id");
                    String name = rs.getString("name");
                    String psw = rs.getString("password");
                    String email = rs.getString("email");
                    Date birthhday = rs.getDate("birthday");
                    System.out.println(id + "  |  "+  name  + "  |  "+ psw +"  |  "+   email   +"  |   "+birthhday);
                }
            }catch (ClassNotFoundException e){
                e.printStackTrace();
            }finally {
                // 6.回收数据库资源
                if (rs != null){
                    try {
                        rs.close();
                    }catch (SQLException e){
                        e.printStackTrace();
                    }
                    rs = null;
                }
                if (stmt !=null){
                    try{
                        stmt.close();
                    }catch (SQLException e){
                        e.printStackTrace();
                    }
                    stmt = null;
                }
                if (coon != null){
                    try{
                        coon.close();
                    }catch (SQLException e){
                        e.printStackTrace();
                    }
                    coon = null;
                }
            }
        }
    }
